#include "dbLayer.h"
#include "sqlite3x.hpp"

using namespace sqlite3x;

using namespace std;

DBLayer::DBLayer()
{
}

DBLayer::~DBLayer()
{
}

void DBLayer::createDB()
{
	createDB(L"myDatabase.db");
}

void DBLayer::createDB(wstring dbname)
{
	createFile(dbname);

	int count = 0;
	try {
		sqlite3_connection con(dbname.c_str());

		//create domain table 
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='domain';"
			);

		if(count == 0) {
			con.executenonquery(
			"CREATE TABLE domain ("
			"domain_id integer PRIMARY KEY NOT NULL, "
			"dc text, "
			"fdnq text"
			");"
			);
		}
		
		//create computer table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='computer';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE computer ("
				"computer_id integer PRIMARY KEY NOT NULL, "
				"domain_id integer NOT NULL, "
				"cn text, "
				"os text, "
				"ip text, "
				"FOREIGN KEY (domain_id) REFERENCES domain(domain_id) ON DELETE NO ACTION ON UPDATE NO ACTION"
				");"
			);
		}

		//create collectionTime table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='collectionTime';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE collectionTime ("
				"computer_id integer NOT NULL,"
				"\"date\" datetime NOT NULL,"
				"collectiontime_id  integer PRIMARY KEY NOT NULL DEFAULT 1,"
				"FOREIGN KEY (computer_id) REFERENCES computer(computer_id) ON DELETE NO ACTION ON UPDATE NO ACTION"
				");"
			);
		}

		//create process table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='process';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE process ("
				"collectiontime_id               integer NOT NULL, "
				"caption_att                     text, "
				"commandLine_att                 text, "
				"creationClassName_att           text, "
				"creationDate_att                datetime, "
				"csCreationClassName_att         text, "
				"csName_att                      text, "
				"description_att                 text, "
				"executablePath_att              text, "
				"executionState_att              integer, "
				"handle_att                      text, "
				"handleCount_att                 integer, "
				"installDate_att                 datetime, "
				"kernelModeTime_att              integer, "
				"maximumWorkingSetSize_att       integer, "
				"minimumWorkingSetSize_att       integer, "
				"name_att                        text, "
				"oSCreationClassName_att         text, "
				"oSName_att                      text, "
				"otherOperationCount_att         integer, "
				"otherTransferCount_att          integer, "
				"pageFaults_att                  integer, "
				"pageFileUsage_att               integer, "
				"parentProcessId_att             integer, "
				"peakPageFileUsage_att           integer, "
				"peakVirtualSize_att             integer, "
				"peakWorkingSetSize_att          integer, "
				"priority_att                    integer, "
				"privatePageCount_att            integer, "
				"processId_att                   integer NOT NULL, "
				"quotaNonPagedPoolUsage_att      integer, "
				"quotaPagedPoolUsage_att         integer, "
				"quotaPeakNonPagedPoolUsage_att  integer, "
				"quotaPeakPagedPoolUsage_att     integer, "
				"readOperationCount_att          integer, "
				"readTransferCount_att           integer, "
				"sessionId_att                   integer, "
				"status_att                      text, "
				"terminationDate_att             datetime, "
				"threadCount_att                 integer, "
				"userModeTime_att                integer, "
				"virtualSize_att                 integer, "
				"windowsVersion_att              text, "
				"workingSetSize_att              integer, "
				"writeOperationCount_att         integer, "
				"writeTransferCount_att          integer, "
				"PRIMARY KEY (collectiontime_id, processId_att), "
				"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id) ON DELETE NO ACTION ON UPDATE NO ACTION"
				");"
			);
		}
		
		//create share table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='share';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE share ( "
				"path_id             varchar(255) NOT NULL, "
				"collectiontime_id   integer NOT NULL, "
				"accessMask_att      integer, "
				"allowMaximum_att    boolean, "
				"caption_att         text, "
				"description_att     text, "
				"installDate_att     datetime, "
				"maximumAllowed_att  integer, "
				"name_att            text, "
				"path_att            text, "
				"status_att          text, "
				"type_att            text, "
				"PRIMARY KEY (path_id, collectiontime_id), "
				"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id) ON DELETE NO ACTION ON UPDATE NO ACTION "
				");"
			);
		}

		//create registry table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='registry';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE registry ( "
				"register_id        integer PRIMARY KEY NOT NULL, "
				"parent_id          integer, "
				"collectiontime_id  integer, "
				"name_att           text, "
				"path_att           text, "
				"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id) ON DELETE NO ACTION ON UPDATE NO ACTION, "
				"FOREIGN KEY (parent_id) REFERENCES registry(register_id) ON DELETE NO ACTION ON UPDATE NO ACTION "
				");"
			);
		}

		//create value table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='value';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE value ( "
				"values_id    varchar(255) PRIMARY KEY NOT NULL, "
				"register_id  varchar(255), "
				"name_att     text, "
				"type_att     text, "
				"parent_id    integer, "
				"FOREIGN KEY (parent_id) REFERENCES registry(register_id) ON DELETE NO ACTION ON UPDATE NO ACTION "
				");"
			);
		}
		//create directory table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='directory';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE directory ( "
				"directory_id       varchar(255) PRIMARY KEY NOT NULL, "
				"parent_id          varchar(255), "
				"collectiontime_id  integer, "
				"caption_att        text, "
				"description_att    text, "
				"installDate_att    datetime, "
				"name_att           text, "
				"status_att         text, "
				"creationDate_att   datetime, "
				"lastAccessed_att   datetime, "
				"lastModified_att   datetime, "
				"writeable_att      boolean, "
				"FOREIGN KEY (parent_id) REFERENCES directory(directory_id) ON DELETE NO ACTION ON UPDATE NO ACTION, "
				"FOREIGN KEY (collectiontime_id) REFERENCES collectionTime(collectiontime_id) ON DELETE NO ACTION ON UPDATE NO ACTION"
				");"
			);
		}
		//create file table
		count = con.executeint(
			"select count(*) "
			"from sqlite_master "
			"where name='file';"
			);

		if(count == 0) {
			con.executenonquery(
				"CREATE TABLE file ( "
				"file_id           varchar(255) PRIMARY KEY NOT NULL, "
				"extension         varchar(255), "
				"parent_id         varchar(255), "
				"caption_att       text, "
				"description_att   text, "
				"installDate_att   datetime, "
				"name_att          text, "
				"status_att        text, "
				"creationDate_att  datetime, "
				"lastAccessed_att  datetime, "
				"lastModified_att  datetime, "
				"writeable_att     boolean, "
				"FOREIGN KEY (parent_id) REFERENCES directory(directory_id) ON DELETE NO ACTION ON UPDATE NO ACTION "
				");"
			);
		}

/*
      sqlite3_transaction trans(con);
      {
         sqlite3_command cmd(con, "insert into t_test values(?,?);");
         cmd.bind(2, "foobar", 6);

         for(int i = 0; i < 10000; ++i) {
            cmd.bind(1, i);
            cmd.executenonquery();
         }
     

      // if trans goes out of scope (due to an exception or
      // anything else) before calling commit(), it will
      // automatically rollback()
      trans.commit();
*/
   }
   catch(exception &ex) {
      cerr << "Exception Occured: " << ex.what() << endl;
   }
}


void DBLayer::createFile(wstring name)
{	
	FILE* pFile;
	pFile = _wfopen (name.c_str(),L"w");
	fclose(pFile);
}